﻿using Dapper;
using ExcelImport;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ExcelImport.SqlServer
{
    public class SqlServerDatabase : IDatabase
    {
        private SqlConnection _conn;

        public SqlServerDatabase(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString)) throw new ArgumentException(connectionString);
            _conn = new SqlConnection(connectionString);
        }

        public void BlukCopy<T>(DataTable dt, ImportTable importTable) where T : class, new()
        {
            #region 无法使用特殊列
            //dt.Rows.RemoveAt(0);

            //var tempProperties = importTable.ImportProperties.Where(p => p.IsTemp);

            //foreach (var tempProperty in tempProperties)
            //{
            //    dt.Columns.Remove(tempProperty.PropertyName);
            //}

            //var importProperties = importTable.ImportProperties.Where(p => !p.IsTemp);

            //using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(_conn))
            //{
            //    sqlBulkCopy.DestinationTableName = importTable.Name;
            //    sqlBulkCopy.BulkCopyTimeout = 600;
            //    foreach (var importProperty in importProperties)
            //    {
            //        sqlBulkCopy.ColumnMappings.Add(importProperty.PropertyName, importProperty.PropertyName);
            //    }
            //    sqlBulkCopy.BatchSize = dt.Rows.Count;
            //    sqlBulkCopy.WriteToServer(dt);
            //}
            #endregion

            dt.Rows.RemoveAt(0);

            var importProperties = importTable.ImportProperties.Where(p => !p.IsTemp);

            var propertyNames = importProperties.Select(p => p.PropertyName);

            string properties = string.Join(",", propertyNames);
            string values = string.Join(",", propertyNames.Select(p => $"@{p}"));
            string sql = $"INSERT INTO {importTable.Name}({properties}) VALUES ({values})";

            List<T> list = dt.ToList<T>(importTable);

            _conn.Execute(sql, list);
        }

        public SqlDbType GetSqlDbType(Type type)
        {
            if (type == typeof(DateTime))
            {
                return SqlDbType.Date;
            }
            else if (type == typeof(int) || type == typeof(int?))
            {
                return SqlDbType.Int;
            }
            else if (type == typeof(float) || type == typeof(float?))
            {
                return SqlDbType.Float;
            }
            else if (type == typeof(double) || type == typeof(double?))
            {
                return SqlDbType.Decimal;
            }
            else if (type == typeof(decimal) || type == typeof(decimal?))
            {
                return SqlDbType.Decimal;
            }
            else if (type == typeof(string))
            {
                return SqlDbType.VarChar;
            }
            throw new NotSupportedException($"暂不支持的数据类型{type.Name},可继承IDatabase自行实现");
        }

        public void Dispose()
        {
            this._conn.Dispose();
        }
    }
}
